﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using System.Resources;
using System.Windows.Forms;
using System.Text.RegularExpressions;

namespace iMKelly.CoreClass {
    public class Database {
        public enum UserType { Reader, Librarian, Administrator };
        OdbcConnection Connection = null;
        public bool connect() {
            string connectionString =
            "Driver={Microsoft Access Driver (*.mdb)};"
            + "Dbq=" + Properties.Settings.Default.Path + ";Uid=Admin;Pwd=;";

            OdbcConnection connection = new OdbcConnection(connectionString);

            try {
                connection.Open();
                Connection = connection;
                return true;
            }
            catch (Exception e) {
                MessageBox.Show(e.Message);
                return false;
            }
        }
        public void createNewUser(UserInfo info) {
            String queryString = "INSERT INTO User (UserID, FullName, BirthDate, Address, SSN, Sex, Privilege) VALUES (?, ?, ?, ?, ?, ?, ?)";
            OdbcCommand command = new OdbcCommand(queryString, Connection);
            
            command.Parameters.AddWithValue("UserID", info.UserID);
            command.Parameters.AddWithValue("FullName", info.Name);
            command.Parameters.AddWithValue("BirthDate", info.BirthDate);
            command.Parameters.AddWithValue("Address", info.Address);
            command.Parameters.AddWithValue("SSN", info.SSN);
            command.Parameters.AddWithValue("Sex", info.Sex);
            command.Parameters.AddWithValue("Privilege", info.Privilege);

            OdbcDataReader reader = command.ExecuteReader();
            
        }
        public User findUserBySSN(String SSN) {
            String query = "SELECT * FROM User WHERE SSN = '" + SSN + "'";
            OdbcCommand command = new OdbcCommand(query, Connection);
            OdbcDataReader reader = command.ExecuteReader();
            if (!reader.Read()) return null;
            UserInfo info = new UserInfo(reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(), reader[4].ToString(), bool.Parse(reader[5].ToString()), Int16.Parse(reader[6].ToString()));
            User user = new User(); user.UserInfo = info;
            return user;
        }
        public UserInfo[] getAllUser() {
            String query = "SELECT * FROM User";
            OdbcCommand command = new OdbcCommand(query, Connection);
            OdbcDataReader reader = command.ExecuteReader();
            List<UserInfo> lst = new List<UserInfo>();
            while (reader.Read()) {
                UserInfo info = new UserInfo(reader[0].ToString(), reader[1].ToString(), reader[4].ToString(), reader[3].ToString(), reader[2].ToString(), bool.Parse(reader[5].ToString()), Int16.Parse(reader[6].ToString()));
                lst.Add(info);
            }
            return lst.ToArray();
        }
        public User findUserByNoID(String ID) {
            String query = "SELECT * FROM User WHERE UserID = '" + ID + "'";
            OdbcCommand command = new OdbcCommand(query, Connection);
            OdbcDataReader reader = command.ExecuteReader();
            if (!reader.Read()) return null;
            UserInfo info = new UserInfo(reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(), reader[4].ToString(), bool.Parse(reader[5].ToString()), Int16.Parse(reader[6].ToString()));
            User user = new User(); user.UserInfo = info;
            return user;
        }
        public void updateUserInfo(String UserID, UserInfo info) {
            String queryString = "UPDATE User SET UserID = ?, FullName = ?, BirthDate = ?, Address = ?, SSN = ?, Sex = ?, Privilege = ? WHERE UserID = '" + UserID + "'";
            OdbcCommand command = new OdbcCommand(queryString, Connection);

            command.Parameters.AddWithValue("UserID", info.UserID);
            command.Parameters.AddWithValue("FullName", info.Name);
            command.Parameters.AddWithValue("BirthDate", info.BirthDate);
            command.Parameters.AddWithValue("Address", info.Address);
            command.Parameters.AddWithValue("SSN", info.SSN);
            command.Parameters.AddWithValue("Sex", info.Sex);
            command.Parameters.AddWithValue("Privilege", info.Privilege);

            OdbcDataReader reader = command.ExecuteReader();
        }
        public void removeUser(String UserID) {
            String query = "DELETE FROM User WHERE UserID = '"+ UserID +"'";
            OdbcCommand command = new OdbcCommand(query, Connection);
            command.ExecuteReader();
        }

        public void importBook(BookTitle title) {
            OdbcCommand command;
            String query;

            bool existed = checkBookExistence(title.Name);
            if (!existed) {
                query = "INSERT INTO BookTitle (BookTitleID, Title, Author, Publisher, Amount) VALUES (?, ?, ?, ?, ?)";
                command = new OdbcCommand(query, Connection);
                command.Parameters.AddWithValue("BookTitleID", title.TitleID);
                command.Parameters.AddWithValue("Title", title.Name);
                command.Parameters.AddWithValue("Author", title.Author);
                command.Parameters.AddWithValue("Publisher", title.Publisher);
                command.Parameters.AddWithValue("Amount", "0");

                command.ExecuteReader();
            }

            query = "UPDATE BookTitle SET Amount = Amount + " + title.Amount + " WHERE Title = '" + title.Name +"'";
            command = new OdbcCommand(query, Connection);
            command.ExecuteReader();
        }
        public BookTitle[] findBookByName(String name) {
            String query = "SELECT * FROM BookTitle WHERE Title LIKE '%" + name + "%'";
            OdbcCommand command = new OdbcCommand(query, Connection);
            OdbcDataReader reader = command.ExecuteReader();
            List<BookTitle> lst = new List<BookTitle>();
            while (reader.Read())
                lst.Add(new BookTitle(reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(), Convert.ToInt16(reader[4].ToString())));
            return lst.ToArray();
        }
        public BookTitle[] findBookByAuthor(String name) {
            String query = "SELECT * FROM BookTitle WHERE Author LIKE '%" + name + "%'";
            OdbcCommand command = new OdbcCommand(query, Connection);
            OdbcDataReader reader = command.ExecuteReader();
            List<BookTitle> lst = new List<BookTitle>();
            while (reader.Read())
                lst.Add(new BookTitle(reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(), Convert.ToInt16(reader[4].ToString())));
            return lst.ToArray();
        }
        public BookTitle findBookByNoID(String ID) {
            String query = "SELECT * FROM BookTitle WHERE BookTitleID = (Select BookTitleID From Book Where BookID = '" + ID + "')";
            OdbcCommand command = new OdbcCommand(query, Connection);
            OdbcDataReader reader = command.ExecuteReader();
            if (!reader.Read()) return null;
            else return new BookTitle(reader[0].ToString(), reader[1].ToString(), reader[2].ToString(), reader[3].ToString(), Convert.ToInt16(reader[4].ToString()));
        }
        public void updateBookTitle(String BookTitleID, BookTitle title) {
            String queryString = "UPDATE BookTitle SET BookTitleID = ?, Title = ?, Author = ?, Publisher = ?, Amount = ? WHERE BookTitleID = '" + BookTitleID + "'";
            OdbcCommand command = new OdbcCommand(queryString, Connection);

            command.Parameters.AddWithValue("BookTitleID", title.TitleID);
            command.Parameters.AddWithValue("Title", title.Name);
            command.Parameters.AddWithValue("Author", title.Author);
            command.Parameters.AddWithValue("Publisher", title.Publisher);
            command.Parameters.AddWithValue("Amount", title.Amount);

            OdbcDataReader reader = command.ExecuteReader();
        }
        public void removeBookTitle(String BookTitleID) {
            String query = "DELETE FROM BookTitle WHERE BookTitleID = '" + BookTitleID + "'";
            OdbcCommand command = new OdbcCommand(query, Connection);
            command.ExecuteReader();
        }
        public bool checkBookAvailable(String BookID) {
            String query = "SELECT Amount FROM BookTitle WHERE BookTitleID = (Select BookTitleID From Book Where BookID = '" + BookID + "')";
            OdbcCommand command = new OdbcCommand(query, Connection);
            OdbcDataReader reader = command.ExecuteReader();
            reader.Read();
            if (Convert.ToInt16(reader[0].ToString()) > 2) return true;
            else return false;
        }

        public bool checkUserExistence(String input) {
            OdbcDataReader reader;
            OdbcCommand command;
            String query;
            if (!Char.IsNumber(input[0])) {
                query = "SELECT * FROM User WHERE UserID = '" + input + "'";
            }
            else {
                query = "SELECT * FROM User WHERE SSN = '" + input + "'";
            }

            command = new OdbcCommand(query, Connection);
            reader = command.ExecuteReader();
            if (reader.Read()) return true;
            else return false;
        }
        public bool checkBookExistence(String input) {
            OdbcDataReader reader;
            OdbcCommand command;
            String query;
            Regex rgx = new Regex("B[0-9]{6}");
            Match match = rgx.Match(input);
            if (!match.Success) {
                query = "SELECT * FROM BookTitle WHERE Title = '" + input + "'";
            }
            else {
                query = "SELECT * FROM Book WHERE BookID = '" + input + "'";
            }
            
            command = new OdbcCommand(query, Connection);
            reader = command.ExecuteReader();
            if (reader.Read()) return true;
            else return false;
        }

        public void createExchange(String ID, String UID, String BID, bool Successed, String SID) {
            String query = "INSERT INTO Exchange (ExchangeID, BookID, UserID, Deadline, Lent, Returned, Staff) VALUES (?, ?, ?, ?, ?, ?, ?)";
            OdbcCommand command = new OdbcCommand(query, Connection);
            command.Parameters.AddWithValue("ExchangeID", ID);
            command.Parameters.AddWithValue("BookID", BID);
            command.Parameters.AddWithValue("UserID", UID);
            command.Parameters.AddWithValue("Deadline", DateTime.Now.AddDays(15));
            command.Parameters.AddWithValue("Lent", Successed);
            command.Parameters.AddWithValue("Returned", false);
            command.Parameters.AddWithValue("Staff", SID);

            command.ExecuteReader();

            if (Successed) {
                query = "UPDATE BookTitle SET Amount = Amount - 1 WHERE BookTitleID = (Select BookTitleID From Book WHERE BookID = '" + BID + "')";
                command = new OdbcCommand(query, Connection);
                command.ExecuteReader();
            }
        }
        public String[] getExchangeInfo(String ID) {
            String query = "SELECT BookID, UserID From Exchange WHERE ExchangeID = '" + ID + "'";
            OdbcCommand command = new OdbcCommand(query, Connection);
            
            OdbcDataReader reader = command.ExecuteReader();
            if (!reader.Read()) return null;
            return new String[] { reader[0].ToString(), reader[1].ToString() };
        }
        public void returnExchange(String ID) {
            String query = "UPDATE Exchange SET Returned = true WHERE ExchangeID = '" + ID +"'";
            OdbcCommand command = new OdbcCommand(query, Connection);
            command.ExecuteReader();
        }

        public String getNextUserID(UserType type) {
            String query = "";
            OdbcCommand command;
            switch (type) {
                case UserType.Reader:
                    query = "SELECT COUNT(UserID) FROM User WHERE UserID LIKE 'R%'";
                    break;
                case UserType.Librarian:
                    query = "SELECT COUNT(UserID) FROM User WHERE UserID LIKE 'L%'";
                    break;
                case UserType.Administrator:
                    query = "SELECT COUNT(UserID) FROM User WHERE UserID LIKE 'A%'";
                    break;
            }
            command = new OdbcCommand(query, Connection);
            OdbcDataReader reader = command.ExecuteReader();
            reader.Read();
            int count = Convert.ToInt16(reader[0].ToString()) + 1;
            return type.ToString().Substring(0, 1) + count.ToString().PadLeft(6, '0');
        }
        public String getNextBookID() {
            String query = "SELECT COUNT(BookID) FROM Book";
            OdbcCommand command;
            
            command = new OdbcCommand(query, Connection);
            OdbcDataReader reader = command.ExecuteReader();
            reader.Read();
            int count = Convert.ToInt16(reader[0].ToString()) + 1;
            return "B" + count.ToString().PadLeft(6, '0');
        }
        public String getNextBookTitleID() {
            String query = "SELECT COUNT(BookTitleID) FROM BookTitle";
            OdbcCommand command;

            command = new OdbcCommand(query, Connection);
            OdbcDataReader reader = command.ExecuteReader();
            reader.Read();
            int count = Convert.ToInt16(reader[0].ToString()) + 1;
            return "BT" + count.ToString().PadLeft(6, '0');
        }
        public String getNextExchangeID() {
            String query = "SELECT COUNT(ExchangeID) FROM Exchange";
            OdbcCommand command;

            command = new OdbcCommand(query, Connection);
            OdbcDataReader reader = command.ExecuteReader();
            reader.Read();
            int count = Convert.ToInt16(reader[0].ToString()) + 1;
            return "EX" + count.ToString().PadLeft(6, '0');
        }
    }
}
